package com.utils;


import com.google.common.cache.Cache;
import com.google.common.cache.CacheBuilder;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

@Slf4j
public class FRDMysqlDbUtil {
    /**
     * 数据库连接缓存
     */
 private  static Cache<String, JdbcTemplate> cache = CacheBuilder.newBuilder().expireAfterWrite(60 * 10 * 6, TimeUnit.SECONDS).build();
    /**
     * 数据库字段信息缓存
     */
    private  static Cache<String, List<String>> columInfo = CacheBuilder.newBuilder().expireAfterWrite(60 * 10 * 6, TimeUnit.SECONDS).build();

    private  static Cache<String, Map<String,String>> columMap = CacheBuilder.newBuilder().expireAfterWrite(60 * 10 * 6, TimeUnit.SECONDS).build();
    private static AtomicInteger max=new AtomicInteger(0);


    private static JdbcTemplate getJdbcTemplate(String url, String user, String psd) {
          JdbcTemplate ifPresent = cache.getIfPresent(url);
          if(ifPresent!=null){
              return  ifPresent;
          }else{
              DriverManagerDataSource dataSource = new DriverManagerDataSource();
              dataSource.setDriverClassName("com.mysql.jdbc.Driver");
              dataSource.setUrl(url);
              dataSource.setUsername(user);
              dataSource.setPassword(psd);
              // 创建JDBC模板
              ifPresent = new JdbcTemplate();
              ifPresent.setDataSource(dataSource);
              cache.put(url, ifPresent);
              return ifPresent;
          }
    }

    public static boolean excute( String sql, String url, String userName, String psd ){
        JdbcTemplate jdbcTemplate = getJdbcTemplate(url, userName, psd);
        jdbcTemplate.execute(sql);
//        jdbcTemplate.execute();
        return true;
    }
    public  static List<String> showTables(String url, String userName, String psd){
        final JdbcTemplate jdbcTemplate = getJdbcTemplate(url, userName, psd);
        final List<Map<String, Object>> show_tables = jdbcTemplate.queryForList("show tables");
        List<String> list=new ArrayList<>();
          show_tables.forEach(map->{
            final Object value = map.entrySet().iterator().next().getValue();
            list.add(value.toString());
        });
          return  list;
    }

    public static  int[] addBatch(List<Object[]> obj, String sql, String url, String userName, String psd ){
        JdbcTemplate jdbcTemplate = getJdbcTemplate(url, userName, psd);

        return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                if(i>=obj.size()){
                    return;
                }
                Object[] args = obj.get(i);
                for (int i1 = 1; i1 <= args.length; i1++) {
                    preparedStatement.setString(i1,   args[i1 - 1]!=null?args[i1 - 1].toString():null);
                }
            }

            @Override
            public int getBatchSize() {
                return obj.size();
            }
        });

    }
    public static    List<Map<String, Object>>  queryInfo(String sql,String url, String userName, String psd){
           JdbcTemplate jdbcTemplate = getJdbcTemplate(url, userName, psd);
            return jdbcTemplate.queryForList(sql);
    }

    public static void bitchInsert(List<List<String>> contactList,String url, String userName, String psd ,String tableName){
        log.info("开始处理表 {}",tableName);
        final long startTime = System.currentTimeMillis();
        List<String> contactColumList = columInfo.getIfPresent(url+tableName);
          if(contactColumList==null){
              contactColumList=new ArrayList<>();
              getColumnList(url, userName, psd, tableName, contactColumList);
              columInfo.put(url+tableName,contactColumList);
        }
        StringBuffer sql = new StringBuffer("insert into " + tableName + "(");

        StringBuffer sqlParam = new StringBuffer();
        List<String> keySort=new ArrayList<>(contactColumList.size());
        StringBuffer valueParam = new StringBuffer();
        List<Object[]> values = new ArrayList<>();
        for (String key : contactColumList) {
            if (sqlParam.length() != 0) {
                sqlParam.append(",");
            }
            sqlParam.append("`").append(key).append("`");
            keySort.add(key);
            valueParam.append("?,");
        }
          Map<String, String> stringStringMap = columMap.getIfPresent(tableName);
        if(stringStringMap==null||stringStringMap.isEmpty()){
            getColumnList(  url,   userName,   psd,   tableName, new ArrayList<>() );
            stringStringMap = columMap.getIfPresent(tableName);
        }
        for (List<String> list2: contactList) {
            List<Object> list = new ArrayList<>(keySort.size());
            for (int i = 0; i < keySort.size(); i++) {
                  String colunm = keySort.get(i);
                String columnType =   stringStringMap.get(colunm);
                Object  value = list2.get(i);
                if((columnType.equals("int")||columnType.equals("date")||"datetime".equals(columnType)||"bigint".equals(columnType))&&value instanceof String&&value.toString().trim().length()<1){
                    value=null;
                }
                list.add(value);
            }
            values.add(list.toArray());
        }
        sql.append(sqlParam).append(")");
        if (valueParam != null && valueParam.length() > 0) {
            valueParam.deleteCharAt(valueParam.length() - 1);
        }
        sql.append("VALUES(" + valueParam + ")");
        final int[] addBatch = addBatch(values, sql.toString(), url, userName, psd);
        final long endTime = System.currentTimeMillis();
        log.info("耗时-------"+(endTime-startTime)+"ms");
    }

    public static void getColumnList(String url, String userName, String psd, String tableName, List<String> contactColumList) {
        String columnSql = "select column_name  as  COLUMN_NAME  ,DATA_TYPE  as DATA_TYPE from information_schema.columns where table_name='" + tableName + "'";
        List<Map<String, Object>> columnList = queryInfo(columnSql, url, userName, psd);
         Map<String ,String> columMapDe=new HashMap<>();
        for (Map<String, Object> map : columnList) {
            if(!contactColumList.contains(map.get("COLUMN_NAME").toString())) {
                contactColumList.add(map.get("COLUMN_NAME").toString());
                columMapDe.put(map.get("COLUMN_NAME").toString(), map.get("DATA_TYPE").toString());
            }
        }
        columMap.put(tableName,columMapDe);
    }
    public static List<String> getColumnList(String url, String userName, String psd, String tableName){
        List<String> contactColumList =new ArrayList<>();
        getColumnList(  url,   userName,   psd,   tableName,  contactColumList);
        return contactColumList;
    }

}
